﻿using Coldairarrow.Business.IT;
using Coldairarrow.Entity.Base_Manage;
using Coldairarrow.Entity.DTO;
using Coldairarrow.Entity.PB;
using Coldairarrow.Entity.TD;
using Coldairarrow.Util;
using EFCore.Sharding;
using LinqKit;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Linq.Expressions;
using System.Threading.Tasks;

namespace Coldairarrow.Business.TD
{
    public partial class TD_InStorDetailBusiness : BaseBusiness<TD_InStorDetail>, ITD_InStorDetailBusiness, ITransientDependency
    {
        public TD_InStorDetailBusiness(IDbAccessor db)
            : base(db)
        {
        }

        #region 外部接口

        public async Task<PageResult<TD_InStorDetail>> GetDataListAsync(PageInput<ConditionDTO> input)
        {
            var q = GetIQueryable();
            var where = LinqHelper.True<TD_InStorDetail>();
            var search = input.Search;

            //筛选
            if (!search.Condition.IsNullOrEmpty() && !search.Keyword.IsNullOrEmpty())
            {
                var newWhere = DynamicExpressionParser.ParseLambda<TD_InStorDetail, bool>(
                    ParsingConfig.Default, false, $@"{search.Condition}.Contains(@0)", search.Keyword);
                where = where.And(newWhere);
            }

            return await q.Where(where).GetPageResultAsync(input);
        }

        /// <summary>
        /// 成品入库统计
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResult<TD_InStorDetailTotalDTO>> GetDataTotalDTOListAsync(PageInput<TD_InStorDetailTotalConditionDTO> input)
        {
            string str = @"select * from (select  DATE_FORMAT(b.InStorTime,'%Y-%m-%d') as InStorTime,d.id as TaskCode,d.poname as TaskName,e.`Code` as ProductCode,e.`Name` as ProductName,e.Spec as ProductSpec,sum(a.Num) as nums from TD_InStorDetail a left join TD_InStorage b on a.InStorId=b.id
            left join PB_ProductionOrder d on a.TaskId=d.Id 
            left join PB_Material e on a.MaterialId=e.Id
            where b.InType='Product' ";
            if (!input.Search.TaskCode.IsNullOrEmpty())
            {
                str += " and b.id like '%" + input.Search.TaskCode + "%' ";
            }
            if (!input.Search.TaskName.IsNullOrEmpty())
            {
                str += " and b.POName like '%" + input.Search.TaskName + "%' ";
            }
            if (!input.Search.ProductCode.IsNullOrEmpty())
            {
                str += " and e.`Code` like '%" + input.Search.ProductCode + "%' ";
            }
            if (!input.Search.ProductName.IsNullOrEmpty())
            {
                str += " and e.`Name` like '%" + input.Search.ProductName + "%' ";
            }
            if (!input.Search.ProductSpec.IsNullOrEmpty())
            {
                str += " and e.Spec  like '%" + input.Search.ProductSpec + "%' ";
            }
            if (!input.Search.StartDate.IsNullOrEmpty() && !input.Search.EndDate.IsNullOrEmpty())
            {
                str += " and DATE_FORMAT(b.InStorTime,'%Y-%m-%d')>='" + input.Search.StartDate + "' and DATE_FORMAT(b.InStorTime,'%Y-%m-%d')<='" + input.Search.EndDate + "' ";
            }
            str += " GROUP BY DATE_FORMAT(b.InStorTime,'%Y-%m-%d') ,d.id ,d.poname ,e.`Code`,e.`Name`,e.Spec ) m ";
            str += " ORDER BY "+input.SortField+" "+input.SortType+"";
            var lidata = await Db.GetListBySqlAsync<TD_InStorDetailTotalDTO>(str);
            int num = lidata.Count;
            var list = lidata
               .Skip((input.PageIndex - 1) * input.PageRows)
               .Take(input.PageRows).ToList();
            return new PageResult<TD_InStorDetailTotalDTO> { Data = list, Total = num };
        }
        public async Task<TD_InStorDetail> GetTheDataAsync(string id)
        {
            return await GetEntityAsync(id);
        }

        public async Task AddDataAsync(TD_InStorDetail data)
        {
            await InsertAsync(data);
        }

        public async Task UpdateDataAsync(TD_InStorDetail data)
        {
            await UpdateAsync(data);
        }

        public async Task DeleteDataAsync(List<string> ids)
        {
            await DeleteAsync(ids);
        }
        public async Task<PageResult<TD_InStorDetailDTO>> GetDataListAsyncDtoList(PageInput<TD_InStorDetailConditionDTO> input) 
        {

            string sql = @"SELECT
	                            TD_InStorage.Id Code,
	                            TD_InStorage.InType,
	                            TD_InStorDetail.MaterialId,
	                            TD_InStorDetail.LocalId,
	                            Base_EnumItem.`Name`,
	                            TD_InStorage.CreateTime InStorTime,
	                            Department.`Name` StorIdName,
	                            Area.`Name` AreaIdName,
	                            Rack.`Name` RackIdName,
	                            lon.`Name` LocalIdName,
	                            ifnull( SUM( TD_InStorDetail.Num ),0) Num,
	                            PB_Material.`Name`  MaterialIdName,
	                            PB_Material.`Code` MaterialCode,
	                            PB_Material.`Spec` Spce,
	                            u.RealName RealName
                            FROM
	                            TD_InStorage
	                            LEFT JOIN TD_InStorDetail ON TD_InStorDetail.InStorId = TD_InStorage.Id
	                            LEFT JOIN (select * from Base_EnumItem where EnumCode='InStorageType') Base_EnumItem ON TD_InStorage.`InType` = Base_EnumItem.`Code`
	                            LEFT JOIN PB_Material ON TD_InStorDetail.MaterialId = PB_Material.Id
	                            LEFT JOIN PB_Location AS lon ON TD_InStorDetail.LocalId = lon.Id
	                            LEFT JOIN PB_Rack AS Rack ON lon.RackId = Rack.Id
	                            LEFT JOIN PB_StorArea AS Area ON Rack.AreaId = Area.Id
	                            LEFT JOIN Base_Department AS Department ON Area.StorId = Department.Id 
	                            LEFT JOIN Base_User AS u on  TD_InStorage.CreatorId=u.Id
                            where 1=1 {0}
                            GROUP BY
	                            TD_InStorage.Id,
	                            TD_InStorDetail.MaterialId,
	                            TD_InStorDetail.LocalId,
	                            Base_EnumItem.`Name` {1}
	                        ";
            //LIMIT "+ (input.PageIndex-1)*input.PageRows+","+ input.PageRows;
            string wherestr = string.Empty;
            if (!string.IsNullOrEmpty(input.Search.Code))
            {
                wherestr += " AND TD_InStorage.Id  like '%" + input.Search.Code + "%' ";
            }
            if (!string.IsNullOrEmpty(input.Search.InType))
            {
                wherestr += " AND TD_InStorage.InType ='" + input.Search.InType+"' ";
            }
            if (!string.IsNullOrEmpty(input.Search.InTimeStart.ToString()) && !string.IsNullOrEmpty(input.Search.InTimeEnd.ToString()))
            {
                wherestr += " and TD_InStorage.InStorTime between '" + Convert.ToDateTime(input.Search.InTimeStart).ToString("yyyy-MM-dd 00:00:00") + "'  and '" + Convert.ToDateTime(input.Search.InTimeEnd).ToString("yyyy-MM-dd 23:59:59") + "'";
            }
            if (!string.IsNullOrEmpty(input.Search.LocalName))
            {
                wherestr += " and lon.Id Like '%" + input.Search.LocalName+"%'";
            }
            if (!string.IsNullOrEmpty(input.Search.MaterialName)) {
                wherestr += "AND( PB_Material.`Name` Like '%" + input.Search.MaterialName+ "%' or PB_Material.`Code` Like '%" + input.Search.MaterialName+"%') ";
            }
            if (!string.IsNullOrEmpty(input.Search.StorageName))
            {
                wherestr += "AND Department.`Id` Like '%" + input.Search.StorageName + "%'";
            }
            if (!string.IsNullOrEmpty(input.Search.StorIdName))
            {
                wherestr += "AND Area.`Id` Like '%" + input.Search.StorIdName + "%'";
            }
            if (!string.IsNullOrEmpty(input.Search.RackName))
            {
                wherestr += "AND Rack.`Id` Like '%" + input.Search.RackName + "%'";
            }
            if (!string.IsNullOrEmpty(input.Search.RealName))
            {
                wherestr += "AND u.`RealName`  Like '%" + input.Search.RealName + "%'";
            }
            string strsorttype= @" ORDER BY TD_InStorage.CreateTime " + input.SortType + " ";
            sql = String.Format(sql, wherestr, strsorttype);
            var res = await Db.GetListBySqlAsync<TD_InStorDetailDTO>(sql);
            #region   分页处理数据   有bug 没有按照主数据进行处理
            int pagecount;//总页数

            List<TD_InStorDetailDTO> listpro_down = res.ToList();//一个要分页的，类型数据集合，存储的是所有的数据
            List<TD_InStorDetailDTO> productes = new List<TD_InStorDetailDTO>();//用来存储要展示的数据
            int pagees = listpro_down.Count;//所有数据的总条数
                                            //计算总页数
            if (pagees % input.PageRows == 0)
            {
                pagecount = pagees / input.PageRows;
            }
            else
            {
                pagecount = (pagees / input.PageRows) + 1;
            }
            productes = listpro_down.Skip((input.PageIndex - 1) * input.PageRows).Take(input.PageRows).ToList();//分页的重点
            return new PageResult<TD_InStorDetailDTO> { Data = productes, Total = pagees };
            //return new PageResult<TD_InStorDetailDTO> { Data = res, Total = pagecount };
            #endregion
        }

        public async Task<List<TD_InStorDetailDTO>> GetDataListAsyncGroup()
        {
            string sql = @"SELECT
	b.`Code` Code,
	b.InStorTime,
	b.InType,
	D.`Name` AS StorIdName,
	M.`Name` AS Material,
	IFNULL(SUM(a.Num),0) Num,
	M.Price,
	U.RealName  AS RealName,
	T.`Name` AS AreaIdName,
	R.`Name` AS RackIdName,
	L.`Name` AS LocalIdName,
	M.Spec AS Spec,
	M.Price AS Price,
	IFNULL(SUM(a.Num*M.Price),0) AS TotalAmt
FROM
	TD_InStorDetail AS a
	INNER JOIN TD_InStorage AS b ON a.InStorId = b.Id
	INNER JOIN PB_Material AS M ON a.MaterialId = M.ID
	INNER JOIN Base_Department AS D ON b.StorId = D.Id
	INNER JOIN Base_User AS U ON b.CreatorId = U.Id
	INNER JOIN PB_Location AS L on a.LocalId=L.Id
	INNER JOIN PB_Rack AS R on L.RackId=R.Id
	INNER JOIN PB_StorArea AS T on L.AreaId=T.Id
	GROUP BY b.`Code`,a.InStorId, D.Id,M.Id,T.Id,R.Id,L.Id,b.InStorTime,b.InType
	
	   
	";
            List<TD_InStorDetailDTO> res = await Db.GetListBySqlAsync<TD_InStorDetailDTO>(sql);
            return res;

        }
  
        /// <summary>
        /// 获取导出数据集合
        /// </summary>
        /// <returns></returns>
        public async Task<List<TD_InStorDetailDTO>> GetDataListAsynclist()
        {
            Expression<Func<TD_InStorDetail, TD_InStorage, Base_Department, PB_Location, PB_Material, PB_Rack, PB_Tray,
                PB_TrayZone, PB_StorArea, Base_User,
                TD_InStorDetailDTO>> select = (a, b, c, d, e, f, g, h, p, o) => new TD_InStorDetailDTO
                {

                    StorIdName = c.Name,
                    LocalIdName = d.Name,
                    MaterialIdName = e.Name,
                    Parentid = b.Id,
                    InType = b.InType,
                    RackIdName = f.Name,
                    AreaIdName = p.Name,
                    RealName = o.RealName,
                    Code = b.Code,
                    InStorTime = b.InStorTime,
                };
            select = select.BuildExtendSelectExpre();
           
            var q = from a in Db.GetIQueryable<TD_InStorDetail>().AsExpandable()
                    join b in Db.GetIQueryable<TD_InStorage>() on a.InStorId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<Base_Department>() on a.StorId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<PB_Location>() on a.LocalId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    join e in Db.GetIQueryable<PB_Material>() on a.MaterialId equals e.Id into ae
                    from e in ae.DefaultIfEmpty()
                    join f in Db.GetIQueryable<PB_Rack>() on d.RackId equals f.Id into df
                    from f in df.DefaultIfEmpty()
                    join g in Db.GetIQueryable<PB_Tray>() on a.TrayId equals g.Id into ag
                    from g in ag.DefaultIfEmpty()
                    join h in Db.GetIQueryable<PB_TrayZone>() on a.ZoneId equals h.Id into ah
                    from h in ah.DefaultIfEmpty()
                    join p in Db.GetIQueryable<PB_StorArea>() on d.AreaId equals p.Id into dp
                    from p in dp.DefaultIfEmpty()
                    join o in Db.GetIQueryable<Base_User>() on a.CreatorId equals o.Id into ao
                    from o in ao.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d, e, f, g, h, p, o);
            var res = q.ToListAsync();
            return await res;
        }
        #endregion

        #region 私有成员

        #endregion
    }
}